﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using MeTalker.DBUtility;
using AD.Model;
namespace AD.DAL
{
    public class LogDAL : DALBase
    {
        public static IList<LogMod> QueryByPage(string filter, int pageSize, int index, out int maxcount)
        {
            maxcount = 0;
            try
            {
                maxcount = Count(filter);
                int size = index * pageSize;
                if (maxcount < size)
                    size = maxcount;
                //string sql = "select top {0} * from tab_user_logon_log where (AutoID NOT IN (select top {1} AutoID from tab_user_logon_log order by AutoID)) order by AutoID";
                string sql = "select top {0} * from tab_user_logon_log where (AutoID NOT IN (select top {1} AutoID from tab_user_logon_log order by AutoID)) order by AutoID";
                sql = string.Format(sql, pageSize, size);
                if (!string.IsNullOrEmpty(filter))
                {
                    sql = "select top {0} * from tab_user_logon_log where {2} and (AutoID NOT IN (select top {1} AutoID from tab_user_logon_log where {2} order by AutoID)) order by AutoID";
                    sql = string.Format(sql, pageSize, size, filter);
                }
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                IList<LogMod> accountFlows = new List<LogMod>();
                foreach (DataRow row in dt.Rows)
                {
                    LogMod user = SetModel(row);
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch { return null; }
        }
        public static int Count(string filter)
        {
            return GetLogs(filter).Rows.Count;
        }
        static LogMod SetModel(DataRow row)
        {
            LogMod log = new LogMod();
            log.AutoID = int.Parse(row["AutoID"].ToString());
            log.IpAddress = row["IpAddress"].ToString();
            log.CommitDate = DateTime.Parse(row["CommitDate"].ToString());
            log.UserName = row["UserName"].ToString();
            return log;
        }

        public static int DeleteAll()
        {
            try
            {
                string sql = "delete from tab_user_logon_log";
                return SqlHelper.ExecuteNonQuery(SQL_CONNECT, CommandType.Text, sql, null);
            }
            catch
            {
                return 0;
            }
        }
        public static int Delete(int autoId) {
            try
            {
                string sql = "delete from tab_user_logon_log where AutoID=@AutoID";
                SqlParameter paramAutoID = new SqlParameter("@AutoID", SqlDbType.Int, 1, "AutoID");
                paramAutoID.Value = autoId;
                return SqlHelper.ExecuteNonQuery(SQL_CONNECT, CommandType.Text, sql, new SqlParameter[] { paramAutoID });
            }
            catch
            {
                return 0;
            }
        }
        public static int Delete(string autoId)
        {
             return    Delete(int.Parse(autoId));
             
        }

        public static IList<LogMod> QueryFilterToList(string filter)
        {
            try {
                DataTable dt = GetLogsFilter(filter);
                IList<LogMod> logs = new List<LogMod>();
                foreach (DataRow row in dt.Rows)
                {
                    logs.Add(SetModel(row));
                }
                return logs;
            }
            catch { }
            return null;
        }

        

        public static DataTable GetLogsFilter(string filter){
            string sql = "select * from tab_user_logon_log";
            if (!string.IsNullOrEmpty(filter)) {
                sql += " where " + filter;
            }
            return SqlHelper.ExecuteDataTable(sql);
        }

        public static DataTable GetLogs()
        {
           return GetLogs("");
        }
        public static DataTable GetLogs(string filter)
        {
            try
            {
                string sql = "select * from tab_user_logon_log";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                return SqlHelper.ExecuteDataTable(sql);
            }
            catch
            {
                return null;
            }
        }
        public static int Insert(LogMod login)
        {
            try
            {
                string sql = "insert into tab_user_logon_log(UserName,CommitDate,IpAddress) values(@UserName,@CommitDate,@IpAddress)";
                return SqlHelper.ExecuteNonQuery(SQL_CONNECT, CommandType.Text, sql, GetSqlParameter(login));
            }
            catch
            {
                return 0;
            }
        }

        static SqlParameter[] GetSqlParameter(LogMod login)
        {
            List<SqlParameter> paramss = new List<SqlParameter>();
            SqlParameter paramAutoID = new SqlParameter("@AutoID", SqlDbType.Int, 4, "AutoID");
            paramAutoID.Value = login.AutoID;
            paramss.Add(paramAutoID);

            SqlParameter paramUserName = new SqlParameter("@UserName", SqlDbType.VarChar, 100, "UserName");
            paramUserName.Value = login.UserName;
            paramss.Add(paramUserName);


            SqlParameter paramLoginDate = new SqlParameter("@CommitDate", SqlDbType.DateTime, 8, "CommitDate");
            paramLoginDate.Value = login.CommitDate;
            paramss.Add(paramLoginDate);

            SqlParameter paramIpAddress = new SqlParameter("@IpAddress", SqlDbType.VarChar, 100, "IpAddress");
            paramIpAddress.Value = login.IpAddress;
            paramss.Add(paramIpAddress);

            return paramss.ToArray();
        }
    }
}
